const express = require("express");
const router = express.Router();
const db = require("../utils/database");
const { authenticate } = require("../middleware/auth");
const {
  getTotalLessonsInCourse,
  getCourseID,
  getTotalSentencesInLesson,
} = require("../utils/resourceScanner");

// 获取用户所有课程进度
router.get("/courses", authenticate, (req, res) => {
  const userId = req.user.id;
  //SELECT strftime('%Y-%m-%d %H:%M:%S', 'your_column_name', 'localtime');

  const query = `
    SELECT id,user_id,course_id,is_completed,completion_percentage,
    strftime('%Y-%m-%d %H:%M:%S', started_at, 'localtime') as started_at,
    strftime('%Y-%m-%d %H:%M:%S', updated_at, 'localtime') as updated_at,
    strftime('%Y-%m-%d %H:%M:%S', completed_at, 'localtime') as completed_at,
    strftime('%Y-%m-%d %H:%M:%S', created_at, 'localtime') as created_at    
    FROM user_course_progress 
    WHERE user_id = ? 
    ORDER BY updated_at DESC
  `;

  db.all(query, [userId], (err, rows) => {
    if (err) {
      return res.status(500).json({
        message: "Error fetching course progress",
        error: err.message,
      });
    }

    // 读取课程索引文件以获取课程标题
    const fs = require("fs");
    const path = require("path");
    const coursesIndexFile = path.join(
      __dirname,
      "../../resources/courses/index.json"
    );

    try {
      const indexContent = fs.readFileSync(coursesIndexFile, "utf8");
      const coursesIndex = JSON.parse(indexContent);

      // 将课程标题添加到进度数据中
      const enrichedRows = rows.map((row) => {
        const course = coursesIndex.find((c) => c.id === row.course_id);
        return {
          ...row,
          title: course ? course.title : `Course ${row.course_id}`,
        };
      });

      res.json(enrichedRows);
    } catch (readError) {
      console.error("Error reading courses index:", readError);
      // 如果无法读取课程索引，仍然返回原始数据
      res.json(rows);
    }
  });
});

// 获取用户特定课程的课时进度
router.get("/lessons", authenticate, (req, res) => {
  const userId = req.user.id;
  let { course_id, course_path, lesson_id } = req.query;
  if (!course_id) {
    course_id = getCourseID(course_path);
  }

  let query = `
    SELECT * FROM user_lesson_progress 
    WHERE user_id = ?
  `;
  const params = [userId];

  if (lesson_id) {
    query += " AND lesson_id = ?";
    params.push(lesson_id);
  }

  if (course_id) {
    query += " AND course_id = ?";
    params.push(course_id);
  }

  query += " ORDER BY updated_at DESC";
  db.all(query, params, (err, rows) => {
    if (err) {
      return res.status(500).json({
        message: "Error fetching lesson progress",
        error: err.message,
      });
    }
    res.json(rows);
  });
});

// 获取用户在特定课时的句子学习进度
router.get("/sentences", authenticate, (req, res) => {
  const userId = req.user.id;
  const { lesson_id, course_id } = req.query;

  if (!lesson_id) {
    return res.status(400).json({ message: "lesson_id is required" });
  }

  if (!course_id) {
    return res.status(400).json({ message: "course_id is required" });
  }

  const query = `
    SELECT * FROM user_sentence_progress 
    WHERE user_id = ? AND lesson_id = ? AND course_id = ?
    ORDER BY updated_at DESC
  `;

  db.all(query, [userId, lesson_id, course_id], (err, rows) => {
    if (err) {
      return res.status(500).json({
        message: "Error fetching sentence progress",
        error: err.message,
      });
    }
    res.json(rows);
  });
});

// 更新或创建用户句子学习进度
router.post("/sentences", authenticate, (req, res) => {
  const userId = req.user.id;
  const {
    sentence_id,
    lesson_id,
    course_id,
    is_mastered,
    times_practiced = 1,
  } = req.body;

  if (!lesson_id) {
    return res.status(400).json({ message: "lesson_id is required" });
  }

  if (!course_id) {
    return res.status(400).json({ message: "course_id is required" });
  }

  // 先查询记录是否存在
  const selectQuery = `
    SELECT 1 FROM user_sentence_progress 
    WHERE user_id = ? AND sentence_id = ? AND lesson_id = ? AND course_id = ?
  `;

  db.get(
    selectQuery,
    [userId, sentence_id, lesson_id, course_id],
    (err, row) => {
      if (err) {
        return res.status(500).json({
          message: "Error checking sentence progress",
          error: err.message,
        });
      }

      let query;
      let params;

      if (row) {
        // 记录存在，执行更新操作
        query = `
        UPDATE user_sentence_progress 
        SET is_mastered = ?, times_practiced = ?, last_practiced_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
        WHERE user_id = ? AND sentence_id = ? AND lesson_id = ? AND course_id = ?
      `;
        params = [
          is_mastered ? 1 : 0,
          times_practiced,
          userId,
          sentence_id,
          lesson_id,
          course_id,
        ];
      } else {
        // console.log("insert:", userId, sentence_id, lesson_id, course_id);
        // 记录不存在，执行插入操作
        query = `
        INSERT INTO user_sentence_progress (user_id, sentence_id, lesson_id, course_id, is_mastered, times_practiced)
        VALUES (?, ?, ?, ?, ?, ?)
      `;
        params = [
          userId,
          sentence_id,
          lesson_id,
          course_id,
          is_mastered ? 1 : 0,
          times_practiced,
        ];
      }

      db.run(query, params, function (err) {
        if (err) {
          console.log(err);
          return res.status(500).json({
            message: "Error updating sentence progress",
            error: err.message,
          });
        }

        // 更新课时进度
        updateLessonProgress(userId, lesson_id, course_id, () => {
          // 更新课程进度
          updateCourseProgress(userId, course_id, () => {
            res.json({
              id: this.lastID || row?.id,
              message: "Sentence progress updated successfully",
            });
          });
        });
      });
    }
  );
});

// 更新课时进度的辅助函数
function updateLessonProgress(userId, lessonId, courseId, callback) {
  // 获取课时的总句子数

  const totalSentences = getTotalSentencesInLesson(courseId, lessonId);

  // 获取用户已掌握的句子数
  const masteredSentencesQuery = `
    SELECT COUNT(*) as mastered FROM user_sentence_progress 
    WHERE user_id = ? AND lesson_id = ? AND course_id = ? AND is_mastered = 1
  `;

  db.get(
    masteredSentencesQuery,
    [userId, lessonId, courseId],
    (err, masteredRow) => {
      if (err) {
        console.error("Error getting mastered sentences:", err);
        return callback();
      }

      const masteredSentences = masteredRow.mastered;
      const completionPercentage =
        totalSentences > 0 ? (masteredSentences / totalSentences) * 100 : 0;
      const isCompleted = completionPercentage === 100 ? 1 : 0;

      // 先获取当前已用时间
      const getTimeSpentQuery = `
      SELECT time_spent FROM user_lesson_progress
      WHERE user_id = ? AND lesson_id = ? AND course_id = ?
    `;

      db.get(
        getTimeSpentQuery,
        [userId, lessonId, courseId],
        (err, progressRow) => {
          if (err) {
            console.error("Error getting current time spent:", err);
            return callback();
          }

          let currentTimeSpent = progressRow ? progressRow.time_spent : 0;
          currentTimeSpent += 30;
          // console.log(`Current time spent: ${currentTimeSpent}`);

          // 更新或创建用户课时进度，包含time_spent字段
          const updateLessonQuery = `
        INSERT INTO user_lesson_progress (user_id, lesson_id, course_id, is_completed, completion_percentage, time_spent)
        VALUES (?, ?, ?, ?, ?, ?)
        ON CONFLICT(user_id, lesson_id, course_id) 
        DO UPDATE SET 
          is_completed = excluded.is_completed,
          completion_percentage = excluded.completion_percentage,
          time_spent = COALESCE(excluded.time_spent, time_spent),
          updated_at = CURRENT_TIMESTAMP
      `;

          db.run(
            updateLessonQuery,
            [
              userId,
              lessonId,
              courseId,
              isCompleted,
              completionPercentage,
              currentTimeSpent,
            ],
            (err) => {
              if (err) {
                console.error("Error updating lesson progress:", err);
              }
              callback();
            }
          );
        }
      );
    }
  );
}

// 更新课程进度的辅助函数
function updateCourseProgress(userId, courseId, callback) {
  // 获取课程的总课时数
  const totalLessons = getTotalLessonsInCourse(courseId);

  // 获取用户已完成的课时数
  const completedLessonsQuery = `
      SELECT COUNT(*) as completed FROM user_lesson_progress 
      WHERE user_id = ? AND course_id = ? AND is_completed = 1
    `;

  db.get(completedLessonsQuery, [userId, courseId], (err, completedRow) => {
    if (err) {
      console.error("Error getting completed lessons:", err);
      return callback();
    }

    const completedLessons = completedRow.completed;
    const completionPercentage =
      totalLessons > 0 ? (completedLessons / totalLessons) * 100 : 0;
    const isCompleted = completionPercentage === 100 ? 1 : 0;

    // 更新或创建用户课程进度
    const updateCourseQuery = `
        INSERT INTO user_course_progress (user_id, course_id, is_completed, completion_percentage)
        VALUES (?, ?, ?, ?)
        ON CONFLICT(user_id, course_id) 
        DO UPDATE SET 
          is_completed = excluded.is_completed,
          completion_percentage = excluded.completion_percentage,
          updated_at = CURRENT_TIMESTAMP
      `;

    db.run(
      updateCourseQuery,
      [userId, courseId, isCompleted, completionPercentage],
      (err) => {
        if (err) {
          console.error("Error updating course progress:", err);
        }
        callback();
      }
    );
  });
}

// 获取用户学习统计信息
router.get("/statistics", authenticate, (req, res) => {
  const userId = req.user.id;

  const statsQuery = `
    SELECT 
      (SELECT COUNT(*) FROM user_course_progress WHERE user_id = ?) as courses_enrolled,
      (SELECT COUNT(*) FROM user_course_progress WHERE user_id = ? AND is_completed = 1) as courses_completed,
      (SELECT COUNT(*) FROM user_lesson_progress WHERE user_id = ? AND is_completed = 1) as lessons_completed,
      (SELECT COUNT(*) FROM user_vocabulary WHERE user_id = ? AND is_learned = 1) as words_learned,
      (SELECT COUNT(*) FROM user_sentence_progress WHERE user_id = ? AND is_mastered = 1) as sentences_mastered,
      (SELECT SUM(time_spent) FROM user_lesson_progress WHERE user_id = ?) as total_study_time
  `;

  db.get(
    statsQuery,
    [userId, userId, userId, userId, userId, userId],
    (err, row) => {
      if (err) {
        return res
          .status(500)
          .json({ message: "Error fetching statistics", error: err.message });
      }
      res.json(row || {});
    }
  );
});

module.exports = router;
